package com.yycwxt.modules.admin.cbxs;

import java.util.Map;

public class CbxsProvider {

    public String sqlcbxx(Map<String, Object> map){
//        String names[] = (String[]) map.get("storenames");
        String template1 = """  
                  ,count(case when subject_direction=1 then order_number end)'销售订单'
                  ,sum(case when subject_direction=1 then  cost end) '订单收入'
                  ,sum(if(subject_code='1004-01-001',  cost , null))'直通车'
                  ,sum(if(subject_code='1004-01-013',  cost , null))'ERP成本'
                  
                  , count(case when subject_code = '1007-01-001' then order_number end) '常温品包材订单数'
                 , sum(case when subject_code = '1007-01-001'  then cost end) '常温品包材费用和'
                 , count(case when subject_code = '1007-01-002' then order_number end) '冷冻品包材订单数'
                 , sum(case when subject_code = '1007-01-002'  then cost end) '冷冻品包材费用和'
                 , sum(case when subject_code = '1007-01-004'  then cost end) '冷媒干冰'
                 , sum(case when subject_code = '1007-01-005'  then cost end) '集装箱'
                 , sum(case when subject_code = '1005-01-006'  then cost end) '中通快递运输费'
                
                , count(case when store_name = '湖南仓' then order_number end) '湖南仓订单数'
                , sum(case when subject_code = '1005-01-005' then cost end) '湖南仓运输费'
                , sum(case when subject_code = '1006-01-002' then cost end) '湖南仓服务费综合管理费'
                , sum(case when store_name = '湖南仓' and subject_code = '1007-01-002' then cost end) '湖南仓冷冻包材费'
                
                , count(case when store_name = '沈北仓' then order_number end) '沈北仓订单数'
                , sum(case when subject_code = '1006-01-001' then cost end) '沈北仓服务费综合管理费'
                            
                , count(case when store_name = '金社长仓' then order_number end) '金社长仓订单数'
                , sum(case when store_name = '金社长仓' and LEFT( subject_code ,7) = '1007-01' then cost end) '金社长仓包材费'
                
                , count(case when store_name = '金社长二仓' then order_number end) '金社长二仓订单数'
                , sum(case when store_name = '金社长二仓' and subject_code = '1008-01-003' then cost end) '金社长二仓水电费'
                             
                , count(case when store_name = '金社长返货仓' then order_number end) '金社长返货仓订单数'
                , sum(case when store_name = '金社长返货仓'  then cost end) '金社长返货仓操作费'
                
                , sum(case when subject_code = '1007-01-007' then cost end) '面藕麻辣拌包装袋'
                """;

        StringBuilder  sql = new StringBuilder();
        sql.append("select shop_name '店铺名称'");
//        for (int i = 0;i < names.length; i++){
//            sql.append(String.format(template,names[i],...));
//        }
        sql.append(template1)
                 .append(" from core_flows  ")
                .append("  where account_date = "+ map.get("zq") + " ")
                .append("  group by shop_name");
        return sql.toString();
    }
}
